<?php
// Define the base exception class (as a catch all for the API)
class exceptionMember extends Exception
{

}



class Member extends UserClass
{
    protected $errorMsg = 'Undefined Error';


    /**
     * Return error message
     *
     * @return string
     */
    function GetErrorMessage()
    {
        return $this->errorMsg;
    }

    /**
     * Add new student and returns the student_id.
     * Requires user/member id and associative array (student_name, student_sex, student_dob, student_medical, student_trial_flag)
     *
     * @param int $_userId
     * @param array $_studentData
     * @return int
     */
    public function addStudent($_userId, $_studentData)
    {
        global $DBobject, $Cardclient;

        $sql = "SELECT student_id, student_user_id FROM tbl_student WHERE student_user_id = :student_user_id AND student_name = :student_name AND student_sex = :student_sex  AND student_dob = :student_dob AND student_deleted IS NULL";
        $params = array (
                ":student_user_id" => $_userId,
                ":student_name" => $_studentData['student_name'],
                ":student_sex" => $_studentData['student_sex'],
                ":student_dob" => $_studentData['student_dob']
        );
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            $studentId  = $res[0]['student_id'];
        } else {
            $sql = " INSERT INTO tbl_student ( student_user_id, student_name, student_sex, student_dob, student_medical, student_trial_flag, student_created ) VALUES ( :student_user_id, :student_name, :student_sex, :student_dob, :student_medical, :student_trial_flag, now() )";
            $params = array_merge($params, array (":student_medical" => $_studentData['student_medical'],":student_trial_flag" => $_studentData['student_trial_flag']));
            $DBobject->wrappedSql($sql, $params);
            $studentId = $DBobject->wrappedSqlIdentity();
        }
        $user_info = $this->updateMemberInfo($_userId);
        if ($user_info) {
            $sql = "UPDATE tbl_student SET student_object_id = :student_object_id WHERE student_id = :student_id";
            $params = [':student_id' => $studentId, 'student_object_id' => $user_info['user_object_id']];
            $DBobject->wrappedSql($sql, $params);
        }
        saveInLog('Add', 'tbl_student', $studentId);
        
        return $studentId;
    }
    
    /**
     * 更新学生信息
     * Requires associative array (student_id, student_name, student_surname, student_dob, student_medical)
     *
     * @param array $_studentData
     * @return boolean
     */
    public function editStudent($_studentData)
    {
        global $DBobject, $Cardclient;

        //1、查询家长手机号码；2、更新一卡易会员 3、更新数据库信息
        $student_info = $this->getStudent($_studentData['student_id']);
        if ($student_info) {
            $userid = $student_info['student_user_id'];
            $user_info = $this->retrieveById($userid);
            $phone_num = $user_info['phone']?:'';
            $card_id = $student_info['student_object_id'];
            $res = true;
            // if ($card_id) {
            //     //更新一卡易会员信息
            //     $res = $Cardclient->updateMemberInfo($card_id, $_studentData['student_sex'], $_studentData['student_name'], $phone_num, $_studentData['student_dob'], $_studentData['student_medical']);
            // }
            $sql = "UPDATE tbl_student SET student_name = :student_name, student_sex = :student_sex, student_dob = :student_dob, student_medical = :student_medical WHERE student_id = :student_id ";
            $params = array (
                    ":student_id" => $_studentData['student_id'],
                    ":student_name" => $_studentData['student_name'],
                    ":student_sex" => $_studentData['student_sex'],
                    ":student_dob" => $_studentData['student_dob'],
                    ":student_medical" => $_studentData['student_medical']
            );
            if ($res && $DBobject->wrappedSql($sql, $params)) {
                $this->updateMemberInfo($userid);
                saveInLog('Edit', 'tbl_student', $_studentData['student_id']);
                return true;
            }
            return false;
        }
        return false;
    }
    
    public function updateMemberInfo($_userId)
    {
        global $DBobject, $Cardclient;
        //1、更新一卡易会员，把学生信息写入备注
        $res = false;
        $user_info = $this->retrieveById($_userId);
        if ($user_info) {
            $students = $this->getStudentsByUserId($_userId);
            $extValue = [];
            foreach ($students as $key => $value) {
                $temp = [
                    "姓名" => $value['student_name'],
                    "性别" => $value['student_sex']==1?'男孩':'女孩',
                    "生日" => $value['student_dob'],
                ];
                $extValue[] = json_encode($temp);
            }
            $card_id = $user_info['user_object_id'];
            $res = $Cardclient->updateMemberInfo($card_id, '', '', '', '', '', $extValue);
            return $user_info;
        }
        return false;
    }

    /**
     * Set student_trial_flag value given the student_id and flag value
     *
     * @param int $_studentId
     * @param boolean $_flag
     *
     * @return boolean
     */
    public function setStudentTrialFlag($_studentId, $_flag)
    {
        global $DBobject;
    
        $sql = "UPDATE tbl_student SET student_trial_flag = :student_trial_flag WHERE student_id = :student_id ";
        $params = array (
                ":student_id" => $_studentId,
                ":student_trial_flag" => $_flag
        );
        return $DBobject->wrappedSql($sql, $params);
    }
    
    
    
    /**
     * Return array with student record given the student_id
     *
     * @param int $_studentId
     * @return array
     */
    function getStudent($_studentId)
    {
        global $DBobject;
    
        $sql = "SELECT * FROM tbl_student WHERE student_id = :student_id AND student_deleted IS NULL";
        $params = array (":student_id" => $_studentId);
        $res = $DBobject->wrappedSql($sql, $params);
        return $res[0];
    }
    
    
    /**
     * Return array with student records given the user_id
     *
     * @param int $_userId
     * @return array
     */
    function GetStudentsByUserId($_userId)
    {
        global $DBobject;
    
        $sql = "SELECT * FROM tbl_student WHERE student_user_id = :student_user_id AND student_deleted IS NULL";
        $params = array (":student_user_id" => $_userId);
        return $DBobject->wrappedSql($sql, $params);
    }
    
    
    
    /**
     * Delete student record given a student_id
     *
     * @param int $_studentId
     * @return boolean
     */
    function DeleteStudent($_studentId)
    {
        global $DBobject;
    
        $sql = "UPDATE tbl_student SET student_deleted = now() WHERE student_id = :student_id ";
        $params = array (
                ":student_id" => $_studentId
        );
        return $DBobject->wrappedSql($sql, $params);
    }
    
    
    /**
     * Create a users' note given a user_id and an associative array with notes details (note_content, note_start_date, note_warning_flag)
     *
     * @param int $_adminId
     * @param int $_userId
     * @param array $_noteArr
     * @return int / false
     */
    function AddNotes($_adminId, $_userId, $_noteArr)
    {
        global $DBobject;
        
        if (!empty($_adminId) && !empty($_userId) && !empty($_noteArr['note_content'])) {
            $sql = " INSERT INTO tbl_note ( note_user_id, note_content, note_start_date, note_warning_flag, note_published, note_created )
								VALUES ( :note_user_id, :note_content, :note_start_date, :note_warning_flag, :note_published, now() )";
            $params = array (
                        ":note_user_id" => $_userId,
                        ":note_content" => $_noteArr['note_content'],
                        ":note_start_date" => $_noteArr['note_start_date'],
                        ":note_warning_flag" => $_noteArr['note_warning_flag'],
                        ":note_published" => 1,
            );
            $DBobject->wrappedSql($sql, $params);
            return $DBobject->wrappedSqlIdentity();
        }
        return false;
    }

    
    /**
     * Return recordset with user's info given a string
     *
     * @param string $_query
     * @return array
     */
    function FindMembers($_query, $_franchiseArr = array())
    {
        global $DBobject;
    
        $cartid = '';
        $mobile = '';
        
        $orderArr = explode('-', $_query);
        $tempquery = preg_replace('/\s/', '', $_query);
        if (preg_match("/^0/", $tempquery) && is_numeric($tempquery)) {
                $mobile = $tempquery;
                $params = array (":query" => "%$mobile%");
                $sql = "SELECT user_id, user_username, user_gname, user_surname
								FROM tbl_user
								LEFT JOIN tbl_address ON address_user_id = user_id
								WHERE user_deleted IS NULL
									AND address_deleted IS NULL
									AND REPLACE(address_telephone, ' ', '') LIKE :query  GROUP BY user_id LIMIT 10";
        } elseif (count($orderArr)>1 && is_numeric($orderArr[0])) {
            $cartid = $orderArr[0];
            $params = array (":query" => $_query);
            $sql = "SELECT user_id, user_username, user_gname, user_surname
								FROM tbl_user
								LEFT JOIN tbl_payment ON payment_user_id = user_id
								WHERE user_deleted IS NULL
									AND payment_deleted IS NULL
									AND payment_cart_id = :query  GROUP BY user_id LIMIT 10";
        } else {
            $sql = "SELECT user_id, user_username, user_gname, user_surname 
							FROM tbl_user 
							LEFT JOIN tbl_student ON student_user_id = user_id	
							WHERE user_deleted IS NULL
								AND student_deleted IS NULL
								AND (CONCAT( REPLACE(student_name, ' ', '+'), '+', REPLACE(student_surname, ' ', '+') ) LIKE :query 
								OR CONCAT( REPLACE(user_gname, ' ', '+'), '+', REPLACE(user_surname, ' ', '+') ) LIKE :query 
								OR user_username LIKE :query )  GROUP BY user_id LIMIT 10";
            $_query = str_replace(' ', '+', $_query);
            $params = array (":query" => "%$_query%");
        }
        
        if (!empty($_franchiseArr)) {
            $sqlArr = array();
            foreach ($_franchiseArr as $f) {
                $sqlArr[] = " location_franchisee_id = '{$f}' ";
            }
            if (!empty($mobile)) {
                $sql = "SELECT user_id, user_username, user_gname, user_surname 
								FROM tbl_user
	  						LEFT JOIN tbl_student ON student_user_id = user_id
								LEFT JOIN tbl_enrolment	ON enrolment_student_id = student_id 
								LEFT JOIN tbl_class ON class_object_id = enrolment_class_object_id
								LEFT JOIN tbl_location ON location_object_id = class_location_id
								LEFT JOIN tbl_address ON address_user_id = user_id
								WHERE user_deleted IS NULL
									AND student_deleted IS NULL
									AND enrolment_deleted IS NULL
									AND class_deleted IS NULL
									AND location_deleted IS NULL
									AND class_published = '1'
									AND address_deleted IS NULL
									AND REPLACE(address_telephone, ' ', '') LIKE :query
									AND (". implode(' OR ', $sqlArr).") GROUP BY user_id LIMIT 10";
            } elseif (!empty($cartid)) {
                $sql = "SELECT user_id, user_username, user_gname, user_surname 
								FROM tbl_user 
								LEFT JOIN tbl_payment ON payment_user_id = user_id			  	
								LEFT JOIN tbl_enrolment	ON enrolment_payment_id = payment_id 
								LEFT JOIN tbl_class ON class_object_id = enrolment_class_object_id
								LEFT JOIN tbl_location ON location_object_id = class_location_id
							  WHERE user_deleted IS NULL
									AND payment_deleted IS NULL
									AND enrolment_deleted IS NULL
									AND class_deleted IS NULL
									AND location_deleted IS NULL
									AND class_published = '1'
									AND payment_cart_id = :query
									AND (". implode(' OR ', $sqlArr).") GROUP BY user_id LIMIT 10";
            } else {
                $sql = "SELECT user_id, user_username, user_gname, user_surname 
								FROM tbl_user
	  						LEFT JOIN tbl_student ON student_user_id = user_id
								LEFT JOIN tbl_enrolment	ON enrolment_student_id = student_id 
								LEFT JOIN tbl_class ON class_object_id = enrolment_class_object_id
								LEFT JOIN tbl_location ON location_object_id = class_location_id
								WHERE user_deleted IS NULL
									AND student_deleted IS NULL
									AND enrolment_deleted IS NULL
									AND class_deleted IS NULL
									AND location_deleted IS NULL
									AND class_published = '1'
									AND ( CONCAT( REPLACE(user_gname, ' ', '+'), '+', REPLACE(user_surname, ' ', '+') ) LIKE :query 
										OR user_username LIKE :query
										OR CONCAT( REPLACE(student_name, ' ', '+'), '+', REPLACE(student_surname, ' ', '+') ) LIKE :query )
									AND (". implode(' OR ', $sqlArr).") GROUP BY user_id LIMIT 10";
            }
        }
            $result = array();
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            foreach ($res as $r) {
                $result["{$r['user_id']}"] = $r;
                $sql = "SELECT student_name, student_surname FROM tbl_student WHERE student_user_id = :student_user_id AND student_deleted IS NULL";
                $params = array (":student_user_id" => $r['user_id']);
                $result["{$r['user_id']}"]['cart_id'] = $cartid;
                $result["{$r['user_id']}"]['students'] = $DBobject->wrappedSql($sql, $params);
            }
        }
            return $result;
    }
    
    
    
    /**
     * Return recordser with user's notes.
     *
     * @param int $_userId
     * @return array
     */
    function GetMemberNotes($_userId)
    {
        global $DBobject;
    
        $sql = "SELECT * FROM tbl_note WHERE note_user_id = :note_user_id  AND note_published = '1' AND note_deleted IS NULL ORDER BY note_created DESC";
        $params = array(":note_user_id" => $_userId);
        return  $DBobject->wrappedSql($sql, $params);
    }
    
    
    
    /**
     * Return recordser with user's emails.
     *
     * @param int $_userId
     * @return array
     */
    function GetMemberEmails($_userId)
    {
        global $DBobject;
    
        $sql = "SELECT tbl_email_queue.*, admin_name, admin_surname FROM tbl_email_queue LEFT JOIN tbl_admin ON admin_id = email_admin_id WHERE email_user_id = :email_user_id  AND email_deleted IS NULL ORDER BY email_created DESC";
        $params = array(":email_user_id" => $_userId);
        return  $DBobject->wrappedSql($sql, $params);
    }
    
    
    
    /**
     * Return array with user's info, address and students.
     *
     * @param int $_userId
     * @return array
     */
    function GetMemberDetails($_userId)
    {
        global $DBobject;
        
        $result = array();
        $result['user'] = $this->retrieveById($_userId);
        $addresses = $this->GetUsersAddresses($_userId);
        $result['address'] = $addresses[0];
        $result['students'] = $this->GetStudentsByUserId($_userId);
        $result['notes'] = $this->GetMemberNotes($_userId);
        $result['emails'] = $this->GetMemberEmails($_userId);
        return $result;
    }
    
    
    
    /**
     * Update member's warning message
     *
     * @param int $_userId
     * @param string $_warning
     * @return array
     */
    function UpdateMemberWarning($_userId, $_warning = null)
    {
        global $DBobject;
    
        $sql = "UPDATE tbl_user SET user_warning = :user_warning WHERE user_id = :id ";
        $params = array (
                ":id" => $_userId,
                ":user_warning" => $_warning
        );
        if ($DBobject->wrappedSql($sql, $params)) {
            return array ('success' => "Member's warning has been updated.");
        }
        return array ('error' => 'There was a connection problem. Please, try again!');
    }

    /**
     * Return recordser with user's info.
     *
     * @param int $_start
     * @return array
     */
    public function getMemberList($_start = 0, $_limit = 50)
    {
        global $DBobject;
    
        $sql = "SELECT * FROM tbl_user WHERE user_deleted IS NULL ORDER BY user_created DESC LIMIT $_start, $_limit";
        return  $DBobject->wrappedSql($sql);
    }
}
